import requests
import datetime
import pandas as pd
import pymysql
from sklearn.preprocessing import MinMaxScaler
from joblib import dump

class WeatherUtils(object):
    """"天气类
    Args:
       object (_type_): _description_
    """
    def __init__(self):
        self.url =''
        self.date=[
            '2024-07-01',
            '2024-08-01',
            '2024-09-01',
            '2024-10-01',
            '2024-11-01',
            '2024-12-01',
        ]
    

    def get_data(self):
        """获取天气数据
        """

        data_list=[]
        for d in self.date:
            conf={
                'appid':"39799797",
                'appsecret':"BA7zjrM",
                'version':"history",
                'year':d[:4],
                'month':d[5:7],
                'city':"南昌"
            }
        res = requests.get(self.url + '?',params=conf)
        res_data =res.json()
        for i in res_data['data']:
            data_list.append({
                'date':datetime.datetime.strptime(i['ymd'],'%Y-%m-%d'),
                'bWendu':i['bWendu'],
                'yWendu':i['yWendu'],
                'tianqi':i['tianqi'],
                'fengxiang':i['fengxiang'],
                'fengli':i['fengli'],
                           
             })
            
        df = pd.DataFrame(data_list)
        df.to_csv('./timing/weather.csv')


class MysqlUtils:
    def __init__(self):
        self.conn = pymysql.connect(
            host='127.0.0.1',
            user='root',
            passwd='root',
            db='scenic',
            port=3306,
            charset='utf8'
        )
        self.weather_data=pd.read_csv('./timing/weather.csv')
    def is_holiday(self,data):
        """是否节假日
        """
        if data in ['20224-09-03','2024-10-01','2024-10-02','2024-10-03','2024-10-04','2024-10-05','2024-10-06','2024-10-07','2025-01-01','2025-01-02','2025-01-03']:
            return 1
        return 0

    def get_scenic_data(self):
        """获取数据
        """
        cursor = self.conn.cursor(cursor=pymysql.cursors.DictCursor)
        
        sql = """  
       SELECT DATE(G.create_time) as date,count(*) as count
        FROM order_user_gate_rel g 
        WHERE HOUR(g.create_time) < '2025-01-01' GROUP BY date
        """
        
        cursor.execute(sql)  
        ret = cursor.fetchall()  
        df = pd.DataFrame(ret)
        #print(df.head())
        
        #合并天气数据
        self.weather_data['date']=pd.to_datetime(self.weather_data['date'])
        df['date']=pd.to_datetime(['date'])
        # 格式转换
        df_pivot=pd.merge(self.weather_data,df,on='date')
       
        df_pivot.set_index('date',inplace=True)

        #转化温度
        df_pivot['bWendu']=df_pivot['bWendu'].str.replace("°","").astype(int)
        df_pivot['yWendu']=df_pivot['yWendu'].str.replace("°","").astype(int)
        
        # print(df_pivot.head())
        df_pivot['dow'] = df_pivot.index.dayofweek  # 星期几（0-6）
        df_pivot['month'] = df_pivot.index.month  # 月份
        df_pivot['is_holiday'] = df_pivot.index.map(self.is_holiday)
       

        # 对星期几和月份进行独热编码
        df_pivot = pd.get_dummies(df_pivot, columns=['dow', 'month'], dtype=int)

     
        # 归一化入园数
        scaler=MinMaxScaler()
        feature=df_pivot[['count']]
        df_pivot[['count']]=scaler.fit_transform(feature)

        #归一化天气
        weather_feature=df_pivot[['bWendu','yWendu']]
        dump(scaler,'./timing/scaler.joblib')
        df_pivot[['bWendu','yWendu']]=scaler.fit_transform(weather_feature)
        dump(weather_feature,'./timing/weather_feature.joblib')
        print(df_pivot.head)
        df_pivot.to_csv('./timing/scenit_data.csv')

        


if __name__ == '__main__':
    #wu=WeatherUtils()
    #wu.get_data()
    mu=MysqlUtils()
    mu.get_scenic_data()
